package org.huel.backend.service.impl;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;
import org.springframework.stereotype.Service;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.util.EntityUtils;

import java.sql.Connection;
import java.sql.Statement;

/**
 * @author Liweijian.
 * @Description TODO
 * @date 2025/6/15 17:49
 */
@Service
public class AIService {

    private static final String DB_URL = "jdbc:mysql://43.143.125.94:3306/attendance_new?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "Lwj378$$";
    private static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";

    private String apiUrl = "https://open.bigmodel.cn/api/paas/v4/chat/completions";
    private String apiKey = "13ab048771cc904120d0fbd9cfb24ef5.bNtv6GveoeQK5cJa";

    private static final int SAMPLE_ROW_LIMIT = 10;

    private final ObjectMapper objectMapper = new ObjectMapper();

    private Connection connectToDatabase() throws SQLException {
        try {
            Class.forName(JDBC_DRIVER);
        } catch (ClassNotFoundException e) {
            System.err.println("JDBC Driver not found: " + JDBC_DRIVER);
            throw new SQLException("JDBC Driver not found: " + JDBC_DRIVER, e);
        }
        return DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
    }

    private String fetchTableDataSample(String tableName) {
        StringBuilder dataDescription = new StringBuilder();
        dataDescription.append("Table Name: ").append(tableName).append("\n");

        String sql = "SELECT * FROM `" + tableName + "` LIMIT " + SAMPLE_ROW_LIMIT;

        try (Connection conn = connectToDatabase();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            List<String> columnNames = new ArrayList<>();
            dataDescription.append("Columns: ");
            for (int i = 1; i <= columnCount; i++) {
                String colName = metaData.getColumnName(i);
                columnNames.add(colName);
                dataDescription.append(colName).append(" (").append(metaData.getColumnTypeName(i)).append(")");
                if (i < columnCount) {
                    dataDescription.append(", ");
                }
            }
            dataDescription.append("\n");

            dataDescription.append("Sample Data (first ").append(SAMPLE_ROW_LIMIT).append(" rows):\n");
            int rowCount = 0;
            while (rs.next() && rowCount < SAMPLE_ROW_LIMIT) {
                dataDescription.append("Row ").append(rowCount + 1).append(": ");
                for (int i = 0; i < columnNames.size(); i++) {
                    String colName = columnNames.get(i);
                    Object value = rs.getObject(colName);
                    dataDescription.append(colName).append("='").append(value).append("'");
                    if (i < columnNames.size() - 1) {
                        dataDescription.append(", ");
                    }
                }
                dataDescription.append("\n");
                rowCount++;
            }

            if (rowCount == 0) {
                dataDescription.append("Table is empty or no data found in sample limit.\n");
            }

        } catch (SQLException e) {
            System.err.println("Error fetching table data: " + e.getMessage());
            e.printStackTrace();
            dataDescription.append("Error fetching table data: ").append(e.getMessage()).append("\n");
        }

        return dataDescription.toString();
    }

    private String sendToAI(String aiInput) {
        String result = "Error: Could not get response from AI.";
        if (apiUrl == null || apiUrl.isEmpty() || apiKey == null || apiKey.isEmpty() || apiUrl.contains("YOUR_AI_API_URL") || apiKey.contains("your_api_key")) {
            return "Error: AI API URL or Key not configured.";
        }

        try (CloseableHttpClient httpClient = HttpClients.createDefault()) {
            HttpPost post = new HttpPost(apiUrl);
            post.setHeader("Content-Type", "application/json");
            post.setHeader("Authorization", "Bearer " + apiKey);

            ObjectNode jsonRequestNode = objectMapper.createObjectNode();
            jsonRequestNode.put("model", "glm-4");

            ArrayNode messagesNode = jsonRequestNode.putArray("messages");
            ObjectNode userMessage = messagesNode.addObject();
            userMessage.put("role", "user");
            userMessage.put("content", aiInput);

            String jsonRequest = objectMapper.writeValueAsString(jsonRequestNode);

            System.out.println("Sending JSON request to AI: " + jsonRequest);

            try (CloseableHttpResponse response = httpClient.execute(post)) {
                int statusCode = response.getStatusLine().getStatusCode();
                String responseBody = EntityUtils.toString(response.getEntity(), "UTF-8");

                System.out.println("Received AI API response (Status: " + statusCode + "): " + responseBody);

                if (statusCode >= 200 && statusCode < 300) {
                    try {
                        JsonNode rootNode = objectMapper.readTree(responseBody);
                        JsonNode contentNode = rootNode.path("choices").path(0).path("message").path("content");

                        if (!contentNode.isMissingNode() && contentNode.isTextual()) {
                            result = contentNode.asText();
                        } else {
                            result = "Error: Could not find AI response text in the expected JSON path.";
                            System.err.println(result + " Raw response: " + responseBody);
                        }
                    } catch (Exception jsonParseException) {
                        result = "Error parsing AI API response JSON: " + jsonParseException.getMessage();
                        System.err.println(result + " Raw response: " + responseBody);
                        jsonParseException.printStackTrace();
                    }
                } else {
                    result = "Error from AI API (Status Code: " + statusCode + "): " + responseBody;
                    System.err.println(result);
                }

            }
        } catch (Exception e) {
            System.err.println("Network or HTTP Error calling AI API: " + e.getMessage());
            e.printStackTrace();
            result = "Network or HTTP Error calling AI API: " + e.getMessage();
        }
        return result;
    }

    public String analyzeTableWithAI(String tableName, String analysisQuestion) {
        String tableDataDescription = fetchTableDataSample(tableName);

        String aiInput = "Analyze the following database table data and schema description:\n\n"
                + tableDataDescription
                + "\nBased on this information, please answer the following question or perform this task:\n\n"
                + analysisQuestion;

        System.out.println("Prepared AI prompt:\n---\n" + aiInput + "\n---");

        String aiAnalysisResult = sendToAI(aiInput);

        return aiAnalysisResult;
    }
}